package com.pub.dbtools;

import org.jeecgframework.core.util.DateUtils;
import org.jeecgframework.core.util.PropertiesUtil;
import org.jeecgframework.core.util.StringUtil;

import java.util.ArrayList;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Hashtable;


public class StaticOper {
	/**
	 * 构造 复制 INSERT 语句
	 * 
	 * @param String
	 *            tablename 表名
	 * @param String
	 *            prikeyname 主键字段名
	 * 
	 * 
	 * @param HashMap
	 *            copytmphash 字段名
	 * 
	 * 
	 * @param DataSimpDto
	 *            copydate 要复制的数据源
	 * 
	 * 
	 * @param int i 要复制的数据源所在记录号
	 * @param int all 设置为 0 则不复制公共字段，否则复制所有字段
	 * 
	 * 
	 * @return String INS语句
	 */
	public static String makeCopyInsSql(String tablename, String prikeyname,
			Hashtable copytmphash, DataSimpDto copydate, int i, int all) {
		DBTableInfo tmptableinfo = DBTableInfo.getInst();

		Hashtable filtfield = copytmphash;

		ArrayList fieldhash = (all == 0) ? tmptableinfo
				.getAllFieldName(tablename.toLowerCase()) : tmptableinfo
				.getWFieldName(tablename.toLowerCase());
		String fieldname = "";
		for (int j = 0; j < fieldhash.size(); j++) {
			fieldname = (String) fieldhash.get(j);

			if (!fieldname.equals(prikeyname.toLowerCase())
					&& !filtfield.containsKey(fieldname)) {
				String maxdata = copydate.getDataValue(fieldname, i);
				if (!maxdata.equals("FD_CHECKMINDATE".toLowerCase())) {
					if (!maxdata.equals("FD_CHECKMAXDATE".toLowerCase())) {
						if (!fieldname.equals("FD_CHECKMAXDATE".toLowerCase())) {
							if (!fieldname.equals("FD_CHECKMINDATE"
									.toLowerCase())) {
								if (!fieldname.equals("PUB_ZHXGSJ"
										.toLowerCase())) {
									copytmphash
											.put(fieldname, copydate
													.getDataValue(fieldname, i));
								}
							}
						}

					}
				}
			}
		}

		String copysql = makeInsSql(tablename.toLowerCase(), copytmphash);
		return copysql;
	}

	/**
	 * 依据标准SQL insert 语句，得到PK值
	 * 
	 * 
	 * 
	 * @param String
	 *            标准SQL语句
	 * @return String PK值
	 */
	public static String getSqlPk(String sqlValue) {
		sqlValue = sqlValue.substring(sqlValue.indexOf("values(") + 7);
		sqlValue = sqlValue.trim();
		sqlValue = sqlValue.substring(sqlValue.indexOf("'") + 1);
		sqlValue = sqlValue.substring(0, sqlValue.indexOf("'"));
		return sqlValue;
	}

	/**
	 * 构造 INSERT 语句
	 * 
	 * @param String
	 *            表名
	 * @param HashMap
	 *            字段名
	 * 
	 * 
	 * @return String INS语句
	 */
	public static String makeInsSql(String tablename, Hashtable tmphash) {
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String currentdb = tmpvo.readProperty("jdbc.dbType");
		DBTableInfo tmptableinfo = DBTableInfo.getInst();
		tablename = tablename.toLowerCase().trim();

		HashMap fieldhash = tmptableinfo.getTableInfo(tablename);
		String pkname = tmptableinfo.getTablePk(tablename);

		tmphash.remove(pkname);
		tmphash.remove(pkname.toLowerCase());
		TablePk tmppk = TablePk.getInstance();

		ArrayList valuevec = new ArrayList();
		ArrayList keyvec = new ArrayList();

		StringBuffer sSql = new StringBuffer(" insert into ");
		sSql.append(tablename);
		sSql.append("(");
		sSql.append(pkname);

		StringBuffer sVal = new StringBuffer(" values(");
		sVal.append("'" + tmppk.getNextValue(tablename) + "'");

		if (currentdb.equalsIgnoreCase("sqlserver")) {
		} else {
			if (tmphash.get("pub_jlzt") == null) {
				tmphash.put("pub_jlzt", "草拟"); // 内置的状态字段

			} else {
				String flag = (String) tmphash.get("pub_jlzt");
				if (flag.trim().equals("")) {
					tmphash.put("pub_jlzt", "草拟"); // 内置的状态字段

				}
			}
			tmphash.put("pub_cjrqsj", DateUtils.getDatetime()); // 内置的日期时间字段

		}
		Enumeration e = tmphash.elements();
		while (e.hasMoreElements()) {
			String nowstr = (String) e.nextElement();
			valuevec.add(nowstr);
		}
		Enumeration s = tmphash.keys();
		while (s.hasMoreElements()) {
			String nowstr = (String) s.nextElement();
			keyvec.add(nowstr.toLowerCase());
		}

		for (int i = 0; i < keyvec.size(); i++) {
			String filename = (String) keyvec.get(i); // 字段名称
			String filevalue = (String) valuevec.get(i); // 值

			filevalue = StringUtil.formatForSql(filevalue);

			if (fieldhash.containsKey(filename)) {
				if (filename.toLowerCase().equals("pub_cjrqsj")) {
					if (currentdb.toLowerCase().trim().startsWith("oracle")) {
						sVal.append(",to_date('");
						sVal.append(DateUtils.getDatetime());
						sVal.append("','YYYY-MM-DD HH24:MI:SS')");
						sSql.append(",");
					} else {
						sVal.append(",'");
						sVal.append(DateUtils.getDatetime() + "'");
						sSql.append(",");
					}
					sSql.append(filename);
				} else {
					String[] tmpstr = (String[]) fieldhash.get(filename);
					String type = tmpstr[1].toLowerCase().trim();

					if ((type.equals("date")) || (type.startsWith("timestamp"))
							|| (type.startsWith("timestmp"))) {
						if (type.equals("date")) {
							if (filevalue.trim().equals("")) {
								filevalue = "null";
								sVal.append(",");
								sVal.append(filevalue);
								sVal.append("");
							} else {
								if (currentdb.toLowerCase().trim()
										.startsWith("oracle")) {
									sVal.append(",to_date('");
									sVal.append(filevalue);
									sVal.append("','YYYY-MM-DD')");
								} else {
									sVal.append(",'");
									sVal.append(filevalue);
									sVal.append("'");
								}
							}
						} else {
							if (filevalue.trim().equals("")) {
								filevalue = "null";
								sVal.append(",");
								sVal.append(filevalue);
								sVal.append("");
							} else {
								if (filevalue.indexOf(".") > 0) {
									filevalue = filevalue.substring(0,
											filevalue.indexOf("."));
								}
								if (currentdb.toLowerCase().trim()
										.startsWith("oracle")) {
									sVal.append(",to_date('");
									sVal.append(filevalue);
									sVal.append("','YYYY-MM-DD HH24:MI:SS')");
								} else {
									sVal.append(",'");
									sVal.append(filevalue);
									sVal.append("'");
								}
							}
						}
					} else {
						if ((type.equals("smallint")) || (type.equals("int"))
								|| (type.equals("integer"))
								|| (type.equals("double"))
								|| (type.equals("float"))
								|| (type.equals("decimal"))
								|| (type.equals("number"))
								|| (type.equals("bigint"))) {
							if (filevalue.trim().equals("")) {
								filevalue = "null";
							}
							sVal.append(",");
							sVal.append(filevalue);
						} else {
							sVal.append(",'");
							sVal.append(filevalue);
							sVal.append("'");
						}
					}
					sSql.append(",");
					sSql.append(filename);
				}

			}
		}
		sVal.append(")");
		sSql.append(")");

		return sSql.toString() + sVal.toString();
	}

	public static String makeInsSqlNoPk(String tablename, Hashtable tmphash) {

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String currentdb = tmpvo.readProperty("jdbc.dbType");

		DBTableInfo tmptableinfo = DBTableInfo.getInst();
		tablename = tablename.toLowerCase().trim();

		HashMap fieldhash = tmptableinfo.getTableInfo(tablename);
		String pkname = tmptableinfo.getTablePk(tablename);

		ArrayList valuevec = new ArrayList();
		ArrayList keyvec = new ArrayList();

		StringBuffer sSql = new StringBuffer(" insert into ");
		sSql.append(tablename);
		sSql.append("(pub_jlzt");

		StringBuffer sVal = new StringBuffer(" values(");
		sVal.append("'草拟'");

		tmphash.put("pub_cjrqsj", DateUtils.getDatetime()); // 内置的日期时间字段

		Enumeration e = tmphash.elements();
		while (e.hasMoreElements()) {
			String nowstr = (String) e.nextElement();
			valuevec.add(nowstr);
		}
		Enumeration s = tmphash.keys();
		while (s.hasMoreElements()) {
			String nowstr = (String) s.nextElement();
			keyvec.add(nowstr.toLowerCase());
		}

		for (int i = 0; i < keyvec.size(); i++) {
			String filename = (String) keyvec.get(i); // 字段名称
			String filevalue = (String) valuevec.get(i); // 值

			filevalue = StringUtil.formatForSql(filevalue);

			if (fieldhash.containsKey(filename)) {
				if (filename.toLowerCase().equals("pub_cjrqsj")) {
					if (currentdb.toLowerCase().trim().startsWith("oracle")) {
						sVal.append(",to_date('");
						sVal.append(DateUtils.getDatetime());
						sVal.append("','YYYY-MM-DD HH24:MI:SS')");
						sSql.append(",");
					} else {
						sVal.append(",'");
						sVal.append(DateUtils.getDatetime() + "'");
						sSql.append(",");
					}
					sSql.append(filename);
				} else {
					String[] tmpstr = (String[]) fieldhash.get(filename);
					String type = tmpstr[1].toLowerCase().trim();

					if ((type.equals("date")) || (type.startsWith("timestamp"))
							|| (type.startsWith("timestmp"))) {
						if (type.equals("date")) {
							if (filevalue.trim().equals("")) {
								filevalue = "null";
								sVal.append(",");
								sVal.append(filevalue);
								sVal.append("");
							} else {
								if (currentdb.toLowerCase().trim()
										.startsWith("oracle")) {
									sVal.append(",to_date('");
									sVal.append(filevalue);
									sVal.append("','YYYY-MM-DD')");
								} else {
									sVal.append(",'");
									sVal.append(filevalue);
									sVal.append("'");
								}
							}
						} else {
							if (filevalue.trim().equals("")) {
								filevalue = "null";
								sVal.append(",");
								sVal.append(filevalue);
								sVal.append("");
							} else {
								if (filevalue.indexOf(".") > 0) {
									filevalue = filevalue.substring(0,
											filevalue.indexOf("."));
								}
								if (currentdb.toLowerCase().trim()
										.startsWith("oracle")) {
									sVal.append(",to_date('");
									sVal.append(filevalue);
									sVal.append("','YYYY-MM-DD HH24:MI:SS')");
								} else {
									sVal.append(",'");
									sVal.append(filevalue);
									sVal.append("'");
								}
							}
						}
					} else {
						if ((type.equals("smallint")) || (type.equals("int"))
								|| (type.equals("integer"))
								|| (type.equals("double"))
								|| (type.equals("float"))
								|| (type.equals("decimal"))
								|| (type.equals("number"))
								|| (type.equals("bigint"))) {
							if (filevalue.trim().equals("")) {
								filevalue = "null";
							}
							sVal.append(",");
							sVal.append(filevalue);
						} else {
							sVal.append(",'");
							sVal.append(filevalue);
							sVal.append("'");
						}
					}
					sSql.append(",");
					sSql.append(filename);
				}

			}
		}
		sVal.append(")");
		sSql.append(")");

		return sSql.toString() + sVal.toString();
	}

	public static String makeInsSql(String currentdb, String tablename,
			Hashtable tmphash) {
		DBTableInfo tmptableinfo = DBTableInfo.getInst();
		tablename = tablename.toLowerCase().trim();

		HashMap fieldhash = tmptableinfo.getTableInfo(currentdb, tablename);
		String pkname = tmptableinfo.getTablePk(tablename);

		tmphash.remove(pkname);

		tmphash.remove(pkname.toLowerCase());
		TablePk tmppk = TablePk.getInstance();

		ArrayList valuevec = new ArrayList();
		ArrayList keyvec = new ArrayList();

		StringBuffer sSql = new StringBuffer(" insert into ");
		sSql.append(tablename);
		sSql.append("(");
		sSql.append(pkname);

		StringBuffer sVal = new StringBuffer(" values(");
		sVal.append("'" + tmppk.getNextValue(tablename) + "'");
		String pxxh = String.valueOf(DateUtils.getOrderTime());
		if (pxxh.length() > 8)
			pxxh = pxxh.substring(pxxh.length() - 8, 8);

		if (tmphash.get("pub_jlzt") == null) {
			tmphash.put("pub_jlzt", "草拟"); // 内置的状态字段

		} else {
			String flag = (String) tmphash.get("pub_jlzt");
			if (flag.trim().equals("")) {
				tmphash.put("pub_jlzt", "草拟"); // 内置的状态字段

			}
		}
		tmphash.put("pub_cjrqsj", DateUtils.getDatetime()); // 内置的日期时间字段

		Enumeration e = tmphash.elements();
		while (e.hasMoreElements()) {
			String nowstr = (String) e.nextElement();
			valuevec.add(nowstr);
		}
		Enumeration s = tmphash.keys();
		while (s.hasMoreElements()) {
			String nowstr = (String) s.nextElement();
			keyvec.add(nowstr.toLowerCase());
		}

		for (int i = 0; i < keyvec.size(); i++) {
			String filename = (String) keyvec.get(i); // 字段名称
			String filevalue = (String) valuevec.get(i); // 值

			filevalue = StringUtil.formatForSql(filevalue);

			if (fieldhash.containsKey(filename)) {
				if (filename.toLowerCase().equals("pub_cjrqsj")) {
					if (currentdb.toLowerCase().trim().startsWith("oracle")) {
						sVal.append(",to_date('");
						sVal.append(DateUtils.getDatetime());
						sVal.append("','YYYY-MM-DD HH24:MI:SS')");
						sSql.append(",");
					} else {
						sVal.append(",'");
						sVal.append(DateUtils.getDatetime() + "'");
						sSql.append(",");
					}
					sSql.append(filename);
				} else {
					String[] tmpstr = (String[]) fieldhash.get(filename);
					String type = tmpstr[1].toLowerCase().trim();

					if ((type.equals("date")) || (type.startsWith("timestamp"))
							|| (type.startsWith("timestmp"))) {
						if (type.equals("date")) {
							if (filevalue.trim().equals("")) {
								filevalue = "null";
								sVal.append(",");
								sVal.append(filevalue);
								sVal.append("");
							} else {
								if (currentdb.toLowerCase().trim()
										.startsWith("oracle")) {
									sVal.append(",to_date('");
									sVal.append(filevalue);
									sVal.append("','YYYY-MM-DD')");
								} else {
									sVal.append(",'");
									sVal.append(filevalue);
									sVal.append("'");
								}
							}
						} else {
							if (filevalue.trim().equals("")) {
								filevalue = "null";
								sVal.append(",");
								sVal.append(filevalue);
								sVal.append("");
							} else {
								if (filevalue.indexOf(".") > 0) {
									filevalue = filevalue.substring(0,
											filevalue.indexOf("."));
								}
								if (currentdb.toLowerCase().trim()
										.startsWith("oracle")) {
									sVal.append(",to_date('");
									sVal.append(filevalue);
									sVal.append("','YYYY-MM-DD HH24:MI:SS')");
								} else {
									sVal.append(",'");
									sVal.append(filevalue);
									sVal.append("'");
								}
							}
						}
					} else {
						if ((type.equals("smallint")) || (type.equals("int"))
								|| (type.equals("integer"))
								|| (type.equals("double"))
								|| (type.equals("float"))
								|| (type.equals("decimal"))
								|| (type.equals("number"))
								|| (type.equals("bigint"))) {
							if (filevalue.trim().equals("")) {
								filevalue = "null";
							}
							sVal.append(",");
							sVal.append(filevalue);
						} else {
							sVal.append(",'");
							sVal.append(filevalue);
							sVal.append("'");
						}
					}
					sSql.append(",");
					sSql.append(filename);
				}

			}
		}
		sVal.append(")");
		sSql.append(")");

		return sSql.toString() + sVal.toString();
	}

	public static String makeInsSql(String tablename, Hashtable tmphash,
			String pkid) {
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String currentdb = tmpvo.readProperty("jdbc.dbType");
		return makeInsSql(tablename, tmphash, pkid, currentdb);
	}

	public static String makeInsSql(String tablename, Hashtable tmphash,
			String pkid, String currentdb) {

		DBTableInfo tmptableinfo = DBTableInfo.getInst();
		tablename = tablename.toLowerCase().trim();

		HashMap fieldhash = tmptableinfo.getTableInfo(currentdb, tablename);
		String pkname = tmptableinfo.getTablePk(tablename);

		tmphash.remove(pkname);
		tmphash.remove(pkname.toLowerCase());

		ArrayList valuevec = new ArrayList();
		ArrayList keyvec = new ArrayList();

		StringBuffer sSql = new StringBuffer(" insert into ");
		sSql.append(tablename);
		sSql.append("(");
		sSql.append(pkname);

		StringBuffer sVal = new StringBuffer(" values(");
		sVal.append("'" + pkid + "'");
		String pxxh = String.valueOf(DateUtils.getOrderTime());
		if (pxxh.length() > 8)
			pxxh = pxxh.substring(pxxh.length() - 8, 8);

		if (tmphash.get("pub_jlzt") == null) {
			tmphash.put("pub_jlzt", "草拟"); // 内置的状态字段

		} else {
			String flag = (String) tmphash.get("pub_jlzt");
			if (flag.trim().equals("")) {
				tmphash.put("pub_jlzt", "草拟"); // 内置的状态字段

			}
		}
		tmphash.put("pub_cjrqsj", DateUtils.getDatetime()); // 内置的日期时间字段

		Enumeration e = tmphash.elements();
		while (e.hasMoreElements()) {
			String nowstr = (String) e.nextElement();
			valuevec.add(nowstr);
		}
		Enumeration s = tmphash.keys();
		while (s.hasMoreElements()) {
			String nowstr = (String) s.nextElement();
			keyvec.add(nowstr.toLowerCase());
		}

		for (int i = 0; i < keyvec.size(); i++) {
			String filename = (String) keyvec.get(i); // 字段名称
			String filevalue = (String) valuevec.get(i); // 值

			filevalue = StringUtil.formatForSql(filevalue);

			if (fieldhash.containsKey(filename)) {
				if (filename.toLowerCase().equals("pub_cjrqsj")) {
					if (currentdb.toLowerCase().trim().startsWith("oracle")) {
						sVal.append(",to_date('");
						sVal.append(DateUtils.getDatetime());
						sVal.append("','YYYY-MM-DD HH24:MI:SS')");
						sSql.append(",");
					} else {
						sVal.append(",'");
						sVal.append(DateUtils.getDatetime() + "'");
						sSql.append(",");
					}
					sSql.append(filename);
				} else {
					String[] tmpstr = (String[]) fieldhash.get(filename);
					String type = tmpstr[1].toLowerCase().trim();
					if ((type.equals("date")) || (type.startsWith("timestamp"))
							|| (type.startsWith("timestmp"))) {
						if (type.equals("date")) {
							if (filevalue.trim().equals("")) {
								filevalue = "null";
								sVal.append(",");
								sVal.append(filevalue);
								sVal.append("");
							} else {
								if (currentdb.toLowerCase().trim()
										.startsWith("oracle")) {
									sVal.append(",to_date('");
									sVal.append(filevalue);
									sVal.append("','YYYY-MM-DD')");
								} else {
									sVal.append(",'");
									sVal.append(filevalue);
									sVal.append("'");
								}
							}
						} else {
							if (filevalue.trim().equals("")) {
								filevalue = "null";
								sVal.append(",");
								sVal.append(filevalue);
								sVal.append("");
							} else {
								if (filevalue.indexOf(".") > 0) {
									filevalue = filevalue.substring(0,
											filevalue.indexOf("."));
								}
								if (currentdb.toLowerCase().trim()
										.startsWith("oracle")) {
									sVal.append(",to_date('");
									sVal.append(filevalue);
									sVal.append("','YYYY-MM-DD HH24:MI:SS')");
								} else {
									sVal.append(",'");
									sVal.append(filevalue);
									sVal.append("'");
								}
							}
						}
					} else {
						if ((type.equals("smallint")) || (type.equals("int"))
								|| (type.equals("integer"))
								|| (type.equals("double"))
								|| (type.equals("float"))
								|| (type.equals("decimal"))
								|| (type.equals("number"))
								|| (type.equals("bigint"))) {
							if (filevalue.trim().equals("")) {
								filevalue = "null";
							}
							sVal.append(",");
							sVal.append(filevalue);
						} else {
							sVal.append(",'");
							sVal.append(filevalue);
							sVal.append("'");
						}
					}
					sSql.append(",");
					sSql.append(filename);
				}

			}
		}
		sVal.append(")");
		sSql.append(")");

		return sSql.toString() + sVal.toString();
	}

	/**
	 * 构造 INSERT 语句 对数字型字段如果为空则等于0
	 * 
	 * @param String
	 *            表名
	 * @param HashMap
	 *            字段名
	 * 
	 * 
	 * @return String INS语句
	 */
	public static String makeInsSqlzero(String tablename, Hashtable tmphash) {
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String currentdb = tmpvo.readProperty("jdbc.dbType");

		DBTableInfo tmptableinfo = DBTableInfo.getInst();
		tablename = tablename.toLowerCase().trim();

		HashMap fieldhash = tmptableinfo.getTableInfo(tablename);
		String pkname = tmptableinfo.getTablePk(tablename);

		tmphash.remove(pkname);
		tmphash.remove(pkname.toLowerCase());
		TablePk tmppk = TablePk.getInstance();

		ArrayList valuevec = new ArrayList();
		ArrayList keyvec = new ArrayList();

		StringBuffer sSql = new StringBuffer(" insert into ");
		sSql.append(tablename);
		sSql.append("(");
		sSql.append(pkname);

		StringBuffer sVal = new StringBuffer(" values(");
		sVal.append("'" + tmppk.getNextValue(tablename) + "'");
		if (tmphash.get("pub_jlzt") == null) {
			tmphash.put("pub_jlzt", "草拟"); // 内置的状态字段

		} else {
			String flag = (String) tmphash.get("pub_jlzt");
			if (flag.trim().equals("")) {
				tmphash.put("pub_jlzt", "草拟"); // 内置的状态字段

			}
		}
		tmphash.put("pub_cjrqsj", DateUtils.getDatetime()); // 内置的日期时间字段

		Enumeration e = tmphash.elements();
		while (e.hasMoreElements()) {
			String nowstr = (String) e.nextElement();
			valuevec.add(nowstr);
		}
		Enumeration s = tmphash.keys();
		while (s.hasMoreElements()) {
			String nowstr = (String) s.nextElement();
			keyvec.add(nowstr.toLowerCase());
		}

		for (int i = 0; i < keyvec.size(); i++) {
			String filename = (String) keyvec.get(i); // 字段名称
			String filevalue = (String) valuevec.get(i); // 值

			filevalue = StringUtil.formatForSql(filevalue);

			if (fieldhash.containsKey(filename)) {
				if (filename.toLowerCase().equals("pub_cjrqsj")) {
					if (currentdb.toLowerCase().trim().startsWith("oracle")) {
						sVal.append(",to_date('");
						sVal.append(DateUtils.getDatetime());
						sVal.append("','YYYY-MM-DD HH24:MI:SS')");
						sSql.append(",");
					} else {
						sVal.append(",'");
						sVal.append(DateUtils.getDatetime() + "'");
						sSql.append(",");
					}
					sSql.append(filename);
				} else {

					String[] tmpstr = (String[]) fieldhash.get(filename);
					String type = tmpstr[1].toLowerCase().trim();
					if ((type.equals("date")) || (type.startsWith("timestamp"))
							|| (type.startsWith("timestmp"))) {
						if (type.equals("date")) {
							if (filevalue.trim().equals("")) {
								filevalue = "null";
								sVal.append(",");
								sVal.append(filevalue);
								sVal.append("");
							} else {
								if (currentdb.toLowerCase().trim()
										.startsWith("oracle")) {
									sVal.append(",to_date('");
									sVal.append(filevalue);
									sVal.append("','YYYY-MM-DD')");
								} else {
									sVal.append(",'");
									sVal.append(filevalue);
									sVal.append("'");
								}
							}
						} else {
							if (filevalue.trim().equals("")) {
								filevalue = "null";
								sVal.append(",");
								sVal.append(filevalue);
								sVal.append("");
							} else {
								if (filevalue.indexOf(".") > 0) {
									filevalue = filevalue.substring(0,
											filevalue.indexOf("."));
								}
								if (currentdb.toLowerCase().trim()
										.startsWith("oracle")) {
									sVal.append(",to_date('");
									sVal.append(filevalue);
									sVal.append("','YYYY-MM-DD HH24:MI:SS')");
								} else {
									sVal.append(",'");
									sVal.append(filevalue);
									sVal.append("'");
								}
							}
						}
					} else {
						if ((type.equals("smallint")) || (type.equals("int"))
								|| (type.equals("integer"))
								|| (type.equals("double"))
								|| (type.equals("float"))
								|| (type.equals("decimal"))
								|| (type.equals("number"))
								|| (type.equals("bigint"))) {
							if (filevalue.trim().equals("")) {
								filevalue = "0";
							}
							sVal.append(",");
							sVal.append(filevalue);
						} else {
							sVal.append(",'");
							sVal.append(filevalue);
							sVal.append("'");
						}
					}
					sSql.append(",");
					sSql.append(filename);
				}
			}
		}
		sVal.append(")");
		sSql.append(")");

		return sSql.toString() + sVal.toString();
	}

	/**
	 * 构造 Update 语句
	 * 
	 * @param String
	 *            表名
	 * @param HashMap
	 *            字段名
	 * 
	 * 
	 * @param String
	 *            WHERE 条件
	 * @return String UPD语句
	 * 
	 */
	public static String makeUpdSql(String dbname, String tablename,
			Hashtable tmphash, String KeyName, String KeyValue) {
		String whereStr = " where " + KeyName + "='" + KeyValue + "'";
		return makeUpdSql(dbname, tablename, tmphash, whereStr);
	}

	public static String makeUpdSql(String tablename, Hashtable tmphash,
			String KeyName, String KeyValue) {
		String whereStr = " where " + KeyName + "='" + KeyValue + "'";
		return makeUpdSql(tablename, tmphash, whereStr);
	}

	/**
	 * 构造 Update 语句
	 * 
	 * @param String
	 *            表名
	 * @param HashMap
	 *            字段名
	 * 
	 * 
	 * @param String
	 *            WHERE 条件
	 * @return String UPD语句
	 * 
	 */
	public static String makeoldeUpdSql(String tablename, Hashtable tmphash,
			String KeyName, String KeyValue) {
		String whereStr = " where " + KeyName + "=" + KeyValue + "";
		return makeUpdSql(tablename, tmphash, whereStr);
	}

	/**
	 * 构造 Update 语句
	 * 
	 * @param String
	 *            表名
	 * @param HashMap
	 *            字段名
	 * 
	 * 
	 * @param String
	 *            WHERE 关键字名称
	 * 
	 * 
	 * @param String
	 *            WHERE 关键字值
	 * 
	 * 
	 * @return String UPD语句
	 * 
	 */
	public static String makeUpdSql(String tablename, Hashtable tmphash,
			String whereStr) {
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String currentdb = tmpvo.readProperty("jdbc.dbType");

		DBTableInfo tmptableinfo = DBTableInfo.getInst();
		tablename = tablename.toLowerCase().trim();
		if (!whereStr.toLowerCase().trim().startsWith("where ")) {
			whereStr = " where " + whereStr;
		}
		HashMap fieldhash = tmptableinfo.getTableInfo(tablename);
		String pkname = tmptableinfo.getTablePk(tablename);

		tmphash.remove(pkname);
		tmphash.remove(pkname.toLowerCase());
		tmphash.remove(pkname.toUpperCase());



		ArrayList valuevec = new ArrayList();
		ArrayList keyvec = new ArrayList();

		StringBuffer sSql = new StringBuffer(" update ");
		sSql.append(tablename);
		sSql.append(" set ");

		Enumeration e = tmphash.elements();
		while (e.hasMoreElements()) {
			String nowstr = (String) e.nextElement();
			valuevec.add(nowstr);
		}
		Enumeration s = tmphash.keys();
		while (s.hasMoreElements()) {
			String nowstr = (String) s.nextElement();
			keyvec.add(nowstr.toLowerCase());
		}

		for (int i = 0; i < keyvec.size(); i++) {
			String filename = (String) keyvec.get(i); // 字段名称
			String filevalue = (String) valuevec.get(i); // 值

			filevalue = StringUtil.formatForSql(filevalue);

			if (fieldhash.containsKey(filename)) {
				String[] tmpstr = (String[]) fieldhash.get(filename);
				String type = tmpstr[1].toLowerCase().trim();

				if ((type.equals("date")) || (type.startsWith("timestamp"))
						|| (type.startsWith("timestmp"))) {
					if (type.equals("date")) {
						if (filevalue.trim().equals("")) {
							filevalue = "null";
							sSql.append(filename + "=" + filevalue + ",");
						} else {
							if (filevalue.length() > 10) {
								filevalue = filevalue.substring(0, 10);
							}
							if (currentdb.toLowerCase().trim()
									.startsWith("oracle")) {
								sSql.append(filename + "=to_date('" + filevalue
										+ "','YYYY-MM-DD'),");
							}
							if (currentdb.toLowerCase().trim()
									.startsWith("mysql")) {
								sSql.append(filename + "='" + filevalue + "',");
							}
							if (currentdb.toLowerCase().trim()
									.startsWith("db2")) {
								sSql.append(filename + "='" + filevalue + "',");
							}
						}
					} else {
						if (filevalue.trim().equals("")) {
							filevalue = "null";
							sSql.append(filename + "=" + filevalue + ",");
						} else {
							if (filevalue.indexOf(".") > 0) {
								filevalue = filevalue.substring(0,
										filevalue.indexOf("."));
							}
							if (currentdb.toLowerCase().trim()
									.startsWith("oracle")) {
								sSql.append(filename + "=to_date('" + filevalue
										+ "','YYYY-MM-DD HH24:MI:SS'),");
							}
							if (currentdb.toLowerCase().trim()
									.startsWith("mysql")) {
								sSql.append(filename + "='" + filevalue + "',");
							}
							if (currentdb.toLowerCase().trim()
									.startsWith("db2")) {
								sSql.append(filename + "='" + filevalue + "',");
							}
						}
					}
				} else {
					if ((type.equals("smallint")) || (type.equals("integer"))
							|| (type.equals("int")) || (type.equals("double"))
							|| (type.equals("float"))
							|| (type.equals("decimal"))
							|| (type.equals("number"))
							|| (type.equals("bigint"))) {
						if (filevalue.trim().equals("")) {
							filevalue = "null";
						}
						sSql.append(filename + "=" + filevalue + ",");
					} else {
						sSql.append(filename + "='" + filevalue + "',");
					}
				}

			}
		}
		String nowsql = sSql.toString().trim();
		if (nowsql.endsWith(",")) {
			nowsql = nowsql.substring(0, nowsql.length() - 1);
		}
		nowsql += whereStr;

		return nowsql;
	}

	public static String makeUpdSql(String currentdb, String tablename,
			Hashtable tmphash, String whereStr) {
		tmphash.put("pub_zhxgsj", DateUtils.getDatetime());
		DBTableInfo tmptableinfo = DBTableInfo.getInst();
		tablename = tablename.toLowerCase().trim();
		if (!whereStr.toLowerCase().trim().startsWith("where ")) {
			whereStr = " where " + whereStr;
		}
		HashMap fieldhash = tmptableinfo.getTableInfo(currentdb, tablename);
		String pkname = tmptableinfo.getTablePk(currentdb, tablename);

		tmphash.remove(pkname);
		tmphash.remove(pkname.toLowerCase());
		tmphash.remove(pkname.toUpperCase());

		tmphash.remove("pub_cjrqsj");

		ArrayList valuevec = new ArrayList();
		ArrayList keyvec = new ArrayList();

		StringBuffer sSql = new StringBuffer(" update ");
		sSql.append(tablename);
		sSql.append(" set ");

		Enumeration e = tmphash.elements();
		while (e.hasMoreElements()) {
			String nowstr = (String) e.nextElement();
			valuevec.add(nowstr);
		}
		Enumeration s = tmphash.keys();
		while (s.hasMoreElements()) {
			String nowstr = (String) s.nextElement();
			keyvec.add(nowstr.toLowerCase());
		}

		for (int i = 0; i < keyvec.size(); i++) {
			String filename = (String) keyvec.get(i); // 字段名称
			String filevalue = (String) valuevec.get(i); // 值

			filevalue = StringUtil.formatForSql(filevalue);

			if (fieldhash.containsKey(filename)) {
				String[] tmpstr = (String[]) fieldhash.get(filename);
				String type = tmpstr[1].toLowerCase().trim();

				if ((type.equals("date")) || (type.startsWith("timestamp"))
						|| (type.startsWith("timestmp"))) {
					if (type.equals("date")) {
						if (filevalue.trim().equals("")) {
							filevalue = "null";
							sSql.append(filename + "=" + filevalue + ",");
						} else {
							if (filevalue.length() > 10) {
								filevalue = filevalue.substring(0, 10);
							}
							if (currentdb.toLowerCase().trim()
									.startsWith("oracle")) {
								sSql.append(filename + "=to_date('" + filevalue
										+ "','YYYY-MM-DD'),");
							}
							if (currentdb.toLowerCase().trim()
									.startsWith("mysql")) {
								sSql.append(filename + "='" + filevalue + "',");
							}
							if (currentdb.toLowerCase().trim()
									.startsWith("db2")) {
								sSql.append(filename + "='" + filevalue + "',");
							}
						}
					} else {
						if (filevalue.trim().equals("")) {
							filevalue = "null";
							sSql.append(filename + "=" + filevalue + ",");
						} else {
							if (filevalue.indexOf(".") > 0) {
								filevalue = filevalue.substring(0,
										filevalue.indexOf("."));
							}
							if (currentdb.toLowerCase().trim()
									.startsWith("oracle")) {
								sSql.append(filename + "=to_date('" + filevalue
										+ "','YYYY-MM-DD HH24:MI:SS'),");
							}
							if (currentdb.toLowerCase().trim()
									.startsWith("mysql")) {
								sSql.append(filename + "='" + filevalue + "',");
							}
							if (currentdb.toLowerCase().trim()
									.startsWith("db2")) {
								sSql.append(filename + "='" + filevalue + "',");
							}
						}
					}
				} else {
					if ((type.equals("smallint")) || (type.equals("integer"))
							|| (type.equals("int")) || (type.equals("double"))
							|| (type.equals("float"))
							|| (type.equals("decimal"))
							|| (type.equals("number"))
							|| (type.equals("bigint"))) {
						if (filevalue.trim().equals("")) {
							filevalue = "null";
						}
						sSql.append(filename + "=" + filevalue + ",");
					} else {
						sSql.append(filename + "='" + filevalue + "',");
					}
				}

			}
		}
		String nowsql = sSql.toString().trim();
		if (nowsql.endsWith(",")) {
			nowsql = nowsql.substring(0, nowsql.length() - 1);
		}
		nowsql += whereStr;

		return nowsql;
	}

	public static String makeCopyInsSqlzero(String tablename,
			String prikeyname, Hashtable copytmphash, DataSimpDto copydate,
			int i, int all) {
		DBTableInfo tmptableinfo = DBTableInfo.getInst();

		Hashtable filtfield = copytmphash;

		ArrayList fieldhash = (all == 0) ? tmptableinfo
				.getAllFieldName(tablename.toLowerCase()) : tmptableinfo
				.getWFieldName(tablename.toLowerCase());
		String fieldname = "";
		for (int j = 0; j < fieldhash.size(); j++) {
			fieldname = (String) fieldhash.get(j);
			if (!fieldname.equals(prikeyname.toLowerCase())
					&& !filtfield.containsKey(fieldname)) {
				String maxdata = copydate.getDataValue(fieldname, i)
						.toLowerCase();
				if (!maxdata.equals("PUB_ZHXGSJ".toLowerCase())) {
					if (!maxdata.equals("FD_CHECKMINDATE".toLowerCase())) {
						if (!maxdata.equals("FD_CHECKMAXDATE".toLowerCase())) {
							if (!fieldname.toLowerCase().equals(
									"FD_CHECKMAXDATE".toLowerCase())) {
								if (!fieldname.toLowerCase().equals(
										"FD_CHECKMAXDATE".toLowerCase())) {
									copytmphash
											.put(fieldname, copydate
													.getDataValue(fieldname, i));
								}
							}
						}
					}
				}
			}

		}
		copytmphash.remove("FD_CHECKMINDATE".toLowerCase());
		copytmphash.remove("FD_CHECKMINDATE");
		copytmphash.remove("FD_CHECKMAXDATE");
		copytmphash.remove("fd_checkmaxdate");

		copytmphash.remove("PUB_ZHXGSJ");
		copytmphash.remove("PUB_ZHXGSJ".toLowerCase());

		String copysql = makeInsSqlzero(tablename.toLowerCase(), copytmphash);
		return copysql;
	}

}
